﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CMS.Entity;
using System.Data;

namespace CMS.DAL
{
    public class TrainDAL
    {
        public List<TrainEnty> GetTrainList(int pageindex, DateTime begindate, DateTime enddate, string title, string type)
        {
            StringBuilder sbSql = new StringBuilder();
            Tools tool = new Tools();            

            sbSql.AppendFormat(" select top 10 * from");
            sbSql.AppendFormat("(select top {0} * from train where 1=1 ", pageindex * 10);
            if (type != null && type != "" && type != "ALL")
            {
                tool.AddSqlParameter("@type", SqlDbType.VarChar, type);
                sbSql.AppendFormat(" and type=@type");
            }
            tool.AddSqlParameter("@begindate", SqlDbType.DateTime, begindate);
            tool.AddSqlParameter("@enddate", SqlDbType.DateTime, enddate);
            tool.AddSqlParameter("@title", SqlDbType.VarChar, "%"+title+"%");

            sbSql.AppendFormat(" and title like @title and  begintime >= @begindate and endtime <= @enddate ");
            sbSql.AppendFormat(" order by begintime asc) as a  order by a.begintime desc");

            
            return SqlHelper.GetDataByColumnsLst<TrainEnty>(DBConn.Temporary, sbSql.ToString(), tool.GetSqlParameters());

        }

        public List<TrainEnty> GetTop6Train()
        {
            string sql = "select top 6 * from train where type='ZXHC' and " +
                        "begintime>='" + string.Format("{0:yyyy-MM-dd}", DateTime.Today) + "' order by begintime asc";
            return SqlHelper.GetDataByColumnsLst<TrainEnty>(DBConn.Temporary, sql, null);
        }

        public DataSet GetZJTrain(string area)
        {
            Tools tool = new Tools();
            tool.AddSqlParameter("@sign", SqlDbType.Int, 2);
            tool.AddSqlParameter("@id", SqlDbType.Int, null);
            tool.AddSqlParameter("@area", SqlDbType.VarChar, area);
            tool.AddSqlParameter("@placenum", SqlDbType.VarChar, "");
            DataSet ds = SqlHelper.ExecuteGetDataSet("", CommandType.StoredProcedure, "web_TrainPlace_Select", tool.GetSqlParameters());
            return ds;
        }

        public DataSet GetZJMDMById(string id)
        {
            Tools tool = new Tools();
            tool.AddSqlParameter("@sign", SqlDbType.Int, 1);
            tool.AddSqlParameter("@id", SqlDbType.Int, Convert.ToInt32(id));
            tool.AddSqlParameter("@area", SqlDbType.VarChar, "");
            tool.AddSqlParameter("@placenum", SqlDbType.VarChar, "");
            DataSet ds = SqlHelper.ExecuteGetDataSet("", CommandType.StoredProcedure, "web_TrainPlace_Select", tool.GetSqlParameters());
            return ds;
        }

        public string InsertUserInfo(string username, string area, string placenum, string reservenum, int isattend)
        {
            Tools tool = new Tools();
            tool.AddSqlParameter("@sign", SqlDbType.Int, 0);
            tool.AddSqlParameter("@id", SqlDbType.Int, null);
            tool.AddSqlParameter("@username", SqlDbType.VarChar, username);
            tool.AddSqlParameter("@area", SqlDbType.VarChar, area);
            tool.AddSqlParameter("@reservenum", SqlDbType.VarChar, reservenum);
            tool.AddSqlParameter("@placenum", SqlDbType.VarChar, placenum);
            tool.AddSqlParameter("@isattend", SqlDbType.Int, isattend);
            DataSet ds = SqlHelper.ExecuteGetDataSet("", CommandType.StoredProcedure, "web_TrainUser_InsAndSelect", tool.GetSqlParameters());
            return ds.Tables[0].Rows[0][0].ToString();

        }

        public List<TrainEnty> GetTop1Train()
        {
            string sql = "select top 1 * from train where type='ZJMDM' order by begintime desc";
            return SqlHelper.GetDataByColumnsLst<TrainEnty>(DBConn.Temporary, sql, null);
        }

        public object CountPage(DateTime begindate, DateTime enddate, string title, string type)
        {
            StringBuilder sbSql = new StringBuilder();
            Tools tool = new Tools();  
            sbSql.AppendFormat(" select count(*) from train ");
            sbSql.AppendFormat("where 1=1 ");
            if (type != null && type != "" && type != "ALL")
            {
                tool.AddSqlParameter("@type", SqlDbType.VarChar, type);
                sbSql.AppendFormat(" and type=@type");
            }

            sbSql.AppendFormat(" and title like @title and begintime >= @begindate and endtime <= @enddate ");

            tool.AddSqlParameter("@begindate", SqlDbType.DateTime, begindate);
            tool.AddSqlParameter("@enddate", SqlDbType.DateTime, enddate);
            tool.AddSqlParameter("@title", SqlDbType.VarChar, "%" + title + "%");
            
            return SqlHelper.ExecuteScalar(DBConn.Temporary, System.Data.CommandType.Text, sbSql.ToString(), tool.GetSqlParameters());
        }

        public List<TrainEnty> GetTrainById(string id)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat(" select * from train ");
            sbSql.AppendFormat("where tid='{0}'", id);

            Tools tool = new Tools();
            return SqlHelper.GetDataByColumnsLst<TrainEnty>(DBConn.Temporary, sbSql.ToString(), tool.GetSqlParameters());
        }

        public int AddTrain(params System.Data.SqlClient.SqlParameter[] commandParameters)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("insert into [Train] ([title],[begintime],[endtime],[lecturer],[address],[percount],[content],[type]) ");
            sbSql.AppendFormat(" values (@title,@begin,@end,@lecturer,@address,@percount,@content,@type) ");
            int num = SqlHelper.ExecuteNonQuery(DBConn.Temporary, System.Data.CommandType.Text, sbSql.ToString(), commandParameters);
            return num;
        }

        public int ModifyTrainById(params System.Data.SqlClient.SqlParameter[] commandParameters)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("update train set title=@title,begintime=@begin,endtime=@end,lecturer=@lecturer,address=@address,percount=@percount,content=@content,type=@type");
            sbSql.AppendFormat(" where tid=@tid");
            int num = SqlHelper.ExecuteNonQuery(DBConn.Temporary, System.Data.CommandType.Text, sbSql.ToString(), commandParameters);
            return num;
        }

        public int DelById(params System.Data.SqlClient.SqlParameter[] commandParameters)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendFormat("delete from train ");
            sbSql.AppendFormat(" where tid=@tid");
            int num = SqlHelper.ExecuteNonQuery(DBConn.Temporary, System.Data.CommandType.Text, sbSql.ToString(), commandParameters);
            return num;
        }

        public object CountPerByTrainId(params System.Data.SqlClient.SqlParameter[] commandParameters)
        {
            string sql = "select (percount-hasper) as metper from train," +
                        "(select count(*) as hasper from Orders where Orders.tid=@tid ) as aa where tid=@tid ";
            return SqlHelper.ExecuteScalar(DBConn.Temporary, System.Data.CommandType.Text, sql, commandParameters);
        }

        public object GetAddressByTrainId(string id)
        {
            string sql = "select content+';'+address from train where tid='" + id + "'";
            return SqlHelper.ExecuteScalar(DBConn.Temporary, System.Data.CommandType.Text, sql, null);
        }

        public DataSet GetTJSP()
        {
            Tools tool = new Tools();
            tool.AddSqlParameter("@sign", SqlDbType.Int, 5);
            tool.AddSqlParameter("@v_id", SqlDbType.Int, null);
            tool.AddSqlParameter("@classid", SqlDbType.Int, null);
            tool.AddSqlParameter("@topnum", SqlDbType.Int, 11);
            DataSet ds = SqlHelper.ExecuteGetDataSet("", CommandType.StoredProcedure, "web_video_select_w", tool.GetSqlParameters());
            return ds;
        }
        public DataSet GetWLHCLY()
        {
            Tools tool = new Tools();
            tool.AddSqlParameter("@sign", SqlDbType.Int, 4);
            tool.AddSqlParameter("@v_id", SqlDbType.Int, null);
            tool.AddSqlParameter("@classid", SqlDbType.Int, 83);
            tool.AddSqlParameter("@topnum", SqlDbType.Int, 11);
            DataSet ds = SqlHelper.ExecuteGetDataSet("", CommandType.StoredProcedure, "web_video_select_w", tool.GetSqlParameters());
            return ds;
        }
    }
}
